package org.example.java.sql;

import java.sql.*;

/**
 （1）READ_UNCOMMITTED
 　　这是事务最低的隔离级别，它充许别外一个事务可以看到这个事务未提交的数据。
 　　会出现脏读、不可重复读、幻读 （隔离级别最低，并发性能高）。
 （2）READ_COMMITTED
 　　保证一个事务修改的数据提交后才能被另外一个事务读取。另外一个事务不能读取该事务未提交的数据。
 　　可以避免脏读，但会出现不可重复读、幻读问题（锁定正在读取的行）。
 （3）REPEATABLE_READ
 　　可以防止脏读、不可重复读，但会出幻读（锁定所读取的所有行）。
 （4）SERIALIZABLE
 　　这是花费最高代价但是最可靠的事务隔离级别，事务被处理为顺序执行。
 　　保证所有的情况不会发生（锁表）。

 * 利用数据库本身的锁 悲观锁
 * @author juntao zhang
 */
public class JDBCConsistencyOfTransactionExample {

    public static void deposit(int amount) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "11111111");
        PreparedStatement pstmt = conn.prepareStatement("UPDATE my_account SET amount = ? WHERE id = 1");
        pstmt.setInt(1, amount);
        pstmt.executeUpdate();
        conn.close();
    }

    public static int show(Connection conn) throws Exception {
//        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "11111111");
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT amount FROM my_account WHERE id=1");
        rs.next();
        int amount = rs.getInt(1);
//        conn.close();
        stmt.close();
        return amount;
    }


    public static void main(String[] args) throws Exception {
        //脏读：•指一个事务读取了另外一个事务未提交的数据。
//        dirtyReads();
        //在一个事务内读取表中的某一行数据，多次读取结果不同(另一个事务已经提交)。
//        repeatableRead();
        repeatableRead2();

        //缺点影响了并发的读
//        serializable(); 可以并发读,但是如果一个线程有修改,第二个线程read就会block 直到前面一个线程commit or rollback
    }


    private static void serializable() throws SQLException, InterruptedException {
        deposit(100);
        Thread depositor = new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    System.out.println("depositor:begin deposit");
                    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "11111111");
                    int amount = show(conn);
                    conn.setAutoCommit(false);
                    PreparedStatement pstmt = conn.prepareStatement("UPDATE my_account SET amount = ? WHERE id = 1");
                    pstmt.setInt(1, 100 + amount);
                    pstmt.executeUpdate();
                    System.out.println("depositor:executeUpdate finished " + show(conn));
                    Thread.sleep(3000);
                    conn.commit();
                    System.out.println("depositor:after rollback amount is " + show(conn));
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
        depositor.setName("depositor");
        depositor.start();

        Thread transferer = new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    Thread.sleep(500);
                    System.out.println("transferer:begin transfer");
                    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "11111111");
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                    int current = show(conn);//block 只有在depositor commit or rollback 才会执行
                    System.out.println("transferer:begin transfer amount is " + current);

                    PreparedStatement pstmt = conn.prepareStatement("UPDATE my_account SET amount = ? WHERE id = 1");
                    pstmt.setInt(1, current - 100);
                    System.out.println("transferer:begin executeUpdate");
                    pstmt.executeUpdate();
                    System.out.println("transferer:finished executeUpdate");
                    conn.commit();
                    System.out.println("transferer:transfer success");
                    System.out.println("transferer:after transfer amount is " + show(conn));
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
        transferer.setName("transferer");
        transferer.start();

        Thread.sleep(10000);
    }

    private static void repeatableRead() throws SQLException, InterruptedException {
        deposit(100);
        Thread depositor = new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    System.out.println("depositor:begin");
                    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "11111111");
                    conn.setAutoCommit(false);
                    conn.prepareStatement("UPDATE my_account SET amount = amount+100 WHERE id = 1")
                            .executeUpdate();
                    System.out.println("depositor:executeUpdate finished " + show(conn));
                    Thread.sleep(2000);
                    conn.commit();
                    System.out.println("depositor:deposit success");
                    System.out.println("depositor:end "+ show(conn));
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
        depositor.setName("depositor");
        depositor.start();

        Thread watcher = new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    System.out.println("watcher:begin");
                    Thread.sleep(500);
                    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "11111111");
                    conn.setAutoCommit(false);
//                    conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//不容许脏读 容许不可重复读(两次数据不同)
                    conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); //不容许不可重复读(两次数据相同)
                    int current = show(conn);
                    System.out.println("watcher:watch1 amount is " + current);
                    Thread.sleep(2000);
                    System.out.println("watcher:watch2 amount is " + show(conn));
                    conn.close();
                    System.out.println("watcher:end");
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
        watcher.setName("watcher");
        watcher.start();

        Thread.sleep(10000);
    }

    private static void repeatableRead2() throws SQLException, InterruptedException {
        deposit(100);
        Thread depositor = new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    System.out.println("depositor:begin");
                    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "11111111");
                    conn.setAutoCommit(false);
                    conn.prepareStatement("UPDATE my_account SET amount = amount+100 WHERE id = 1")
                            .executeUpdate();
                    System.out.println("depositor:executeUpdate finished " + show(conn));
                    Thread.sleep(2000);
                    conn.commit();
                    System.out.println("depositor:deposit success");
                    System.out.println("depositor:end "+ show(conn));
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
        depositor.setName("depositor");
        depositor.start();

        Thread watcher = new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    System.out.println("watcher:begin");
                    Thread.sleep(500);
                    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "11111111");
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
                    int current = show(conn);
                    System.out.println("watcher:watch1 amount is " + current);
                    Thread.sleep(2000);
                    PreparedStatement pstmt = conn.prepareStatement("UPDATE my_account SET amount = ? WHERE id = 1");
                    pstmt.setInt(1, current - 150);
                    pstmt .executeUpdate();//覆盖了depositor的结果 如果使用TRANSACTION_SERIALIZABLE 可以避免
                    System.out.println("watcher:watch2 amount is " + show(conn));
                    conn.commit();
                    conn.close();
                    System.out.println("watcher:end");
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
        watcher.setName("watcher");
        watcher.start();

        Thread.sleep(10000);
    }

    private static void dirtyReads() throws SQLException, InterruptedException {
        deposit(100);
        Thread depositor = new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    System.out.println("depositor:begin deposit");
                    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "11111111");
                    int amount = show(conn);
                    conn.setAutoCommit(false);
                    PreparedStatement pstmt = conn.prepareStatement("UPDATE my_account SET amount = ? WHERE id = 1");
                    pstmt.setInt(1, 100 + amount);
                    pstmt.executeUpdate();
                    System.out.println("depositor:executeUpdate finished " + show(conn));
                    Thread.sleep(3000);
                    conn.rollback();
                    System.out.println("depositor:deposit failed");

                    System.out.println("depositor:after rollback amount is " + show(conn));
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
        depositor.setName("depositor");
        depositor.start();

        Thread transferer = new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    Thread.sleep(500);
                    System.out.println("transferer:begin transfer");
                    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "11111111");
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);//容许脏读(没有committed数据)
                    int current = show(conn);
                    System.out.println("transferer:begin transfer amount is " + current);

                    PreparedStatement pstmt = conn.prepareStatement("UPDATE my_account SET amount = ? WHERE id = 1");
                    pstmt.setInt(1, current - 150);
                    System.out.println("transferer:begin executeUpdate");
                    pstmt.executeUpdate();
                    System.out.println("transferer:finished executeUpdate");
                    Thread.sleep(500);
                    conn.commit();
                    System.out.println("transferer:transfer success");
                    System.out.println("transferer:after transfer amount is " + show(conn));
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        });
        transferer.setName("transferer");
        transferer.start();

        Thread.sleep(10000);
    }
}
